package org.UserHouse.Dao.Imp;

import org.UserHouse.Dao.UserDao;
import org.UserHouse.Utils.Dbutils;
import org.UserHouse.entity.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author bin
 * @date 2024-11-26
 */
public class UserDaoImp implements UserDao {
    @Override
    public List<User> selectUser() {
        List<User> users = new ArrayList<>();
        String sql = "select * from u_user where is_delete = 1";
        PreparedStatement pst = null;
        ResultSet rst = null;
        Connection conn = Dbutils.getConnection();
        try {
            pst = conn.prepareStatement(sql);
            rst = pst.executeQuery();
            while (rst.next()) {
                User user = new User();
                int Uid = rst.getInt(1);
                String Uname = rst.getString(2);
                String Upassword = rst.getString(3);
                String Uphone = rst.getString(4);
                String Uemail = rst.getString(5);
                String Utype = rst.getString(6);
                Date Udate = rst.getDate(7);

                user.setUid(Uid);
                user.setUname(Uname);
                user.setUpassword(Upassword);
                user.setUphone(Uphone);
                user.setUemail(Uemail);
                user.setUtype(Utype);
                user.setUdate(Udate);

                users.add(user);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            Dbutils.Close(rst, conn, pst);
        }
        return users;
    }

    @Override
    public User selectByUname(String Uname) {
        User user = null;
        String sql = "select * from u_user where u_name=?";
        Connection conn = Dbutils.getConnection();
        PreparedStatement pst = null;
        ResultSet rst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, Uname);
            rst = pst.executeQuery();
            if (rst.next()) {
                user = new User();
                int Uid = rst.getInt("u_id");
                Uname = rst.getString("u_name");
                String Uassword = rst.getString("u_password");
                String Uphone = rst.getString("u_phone");
                String Upemail = rst.getString("u_email");
                String Utype = rst.getString("u_type");
                Date Udate = rst.getDate("u_date");
                user.setUid(Uid);
                user.setUname(Uname);
                user.setUpassword(Uassword);
                user.setUphone(Uphone);
                user.setUemail(Upemail);
                user.setUtype(Utype);
                user.setUdate(Udate);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            Dbutils.Close(rst, conn, pst);
        }
        return user;
    }

    @Override
    public int insertUser(User user) {
        int n = 0;
        String sql = "INSERT INTO u_user ( u_name, u_password, u_phone, u_email) VALUES (?,?,?,?)";
        Connection conn = Dbutils.getConnection();
        PreparedStatement pst = null;
        ResultSet rst = null;
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, user.getUname());
            pst.setString(2, user.getUpassword());
            pst.setString(3, user.getUphone());
            pst.setString(4, user.getUemail());

            n = pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            Dbutils.Close(null, conn, pst);
        }
        return n;
    }

    @Override
    public int UpdateUser(User user) {
        int n = 0;
        String sql = "update u_user set  u_name=?, u_phone=?, u_email=?, u_type=?, u_date=? where u_id = ?";
        PreparedStatement pst = null;
        ResultSet rst = null;
        Connection conn = Dbutils.getConnection();
        try {
            pst = conn.prepareStatement(sql);
            pst.setString(1, user.getUname());
            pst.setString(2, user.getUphone());
            pst.setString(3, user.getUemail());
            pst.setString(4, user.getUtype());

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            String Udate = sdf.format(user.getUdate());

            pst.setString(5, Udate);

            pst.setInt(6, user.getUid());
            n = pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            Dbutils.Close(rst, conn, pst);
        }
        return n;
    }

    @Override
    public int deleteUser(int Uid) {
        int n = 0;
        String sql = "update u_user set  is_delete = 0 where u_id =?";
        PreparedStatement pst = null;
        ResultSet rst = null;
        Connection conn = Dbutils.getConnection();
        try {
            pst = conn.prepareStatement(sql);
            pst.setInt(1, Uid);
            n = pst.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            Dbutils.Close(rst, conn, pst);
        }
        return n;
    }

    @Override
    public List<User> selectPage(User user, int page, int limit) {
        List<User> list = new ArrayList<>();
        StringBuilder sql = new StringBuilder("select * FROM ( ") ;
        sql.append("select  u_id,  u_name,  u_password,  u_phone, u_email,  u_type,  u_date, row_number() ") ;
        sql.append("over (order by u_date asc) as rownumber from u_user where 1=1 and is_delete = 1 ") ;

        // 1.定义集合，用于存储SQL语句的？占位符的参数值
        List<Object> params = new ArrayList<Object>();

        if(user!=null && user.getUname()!=null ) {
            sql.append("and u_name like ?") ;
            params.add("%"+user.getUname()+"%") ;
        }
        sql.append(") as t2 where rownumber between ? and ?") ;


        int startRow = (page - 1) * limit + 1;
        params.add(startRow) ;

        int endRow = page * limit;
        params.add(endRow) ;

        PreparedStatement pst = null;
        ResultSet rst = null;
        Connection conn = Dbutils.getConnection();

        try {
            pst = conn.prepareStatement(sql.toString());
            setParams(params, pst);
            rst = pst.executeQuery();

            while (rst.next()) {
                User u = new User();
                int Uid = rst.getInt(1);
                String Uname = rst.getString(2);
                String Upassword = rst.getString(3);
                String Uphone = rst.getString(4);
                String Uemail = rst.getString(5);
                String Utype = rst.getString(6);
                Date Udate = rst.getDate(7);

                u.setUid(Uid);
                u.setUname(Uname);
                u.setUphone(Upassword);
                u.setUphone(Uphone);
                u.setUemail(Uemail);
                u.setUtype(Utype);
                u.setUdate(Udate);

                list.add(u);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return list;
    }

    private static void setParams(List<Object> params, PreparedStatement pst) throws SQLException {
        for (int i = 0; i < params.size(); i++) {
            pst.setObject(i+1, params.get(i));
        }
    }

    @Override
    public int forgetpassword(User user) {
        int n = 0;
        String sql = "update u_user set  u_name=?, u_password=?, u_phone=?, u_email=?, u_type=?, u_date=? where u_id = ?";
        return n;
    }

    @Override
    public Long selectPageCount(User user) {
        long r = 0;
        StringBuilder sql = new StringBuilder("select COUNT (u_id ) from u_user where 1=1 and is_delete = 1") ;

        // 1.定义集合，用于存储SQL语句的参数值
        List<Object> params = new ArrayList<Object>();

        if(user!=null && user.getUname()!=null ) {
            sql.append("and u_name like ?") ;
            params.add("%" +user.getUname() + "%") ;
        }

        Connection conn = Dbutils.getConnection();
        ResultSet rst = null;
        PreparedStatement pst = null;
        try {
            pst = conn.prepareStatement(sql.toString());

                setParams(params,pst);

            rst = pst.executeQuery();
            if (rst.next()) {
                r = rst.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            Dbutils.Close(rst, conn, pst);
        }
        return r;
    }


}
